<?php
/**
 * Description of Emms_Cron_LoanWriteOffs
 * @author andres
 * 
 * Loans that reach either a predefined number of days or payments behind 
 * schedule, are automatically written-off.
 */
class Emms_Cron_LoanWriteOffs extends Emms_Cron implements Emms_Interface_Cron
{
    const STATUS_WRITTEN_OFF = 'Written-Off';
    const STATUS_DISBURSED = 'Disbursed';
    
    public function update()
    {
        $this->db->beginTransaction();
        try {
            
            $this->_insertWriteOffs();
            $this->_insertStatusChanges();
            $this->_updateLoans();
            $this->_updateActiveLoans();
            $this->_updateArrears();
            
        } catch (Zend_Exception $e) {
            
            $this->db->rollBack();
            throw $e;
        }
        $this->db->commit();
    }
    
    private function _insertWriteOffs()
    {
        $this->db->query('
            insert 
                loan_write_offs
                (LoanId, Amount, Principal, LoanInsurance, HealthInsurance, AdministrativeFee, Interest, Penalties, LastEditDate, LastEditUserId)
            select 
                a.LoanId,
                (a.BalancePrincipal + a.BalanceLoanInsurance + a.BalanceHealthInsurance + a.BalanceAdministrativeFee + a.Interest + a.Penalty) Amount,
                a.BalancePrincipal Principal,
                a.BalanceLoanInsurance LoanInsurance,
                a.BalanceHealthInsurance HealthInsurance,
                a.BalanceAdministrativeFee AdministrativeFee,
                a.Interest,
                a.Penalty Penalties,
                ? LastEditDate,
                1 LastEditUserId
            from 
                arrears a 
            where 
                a.Date = ? and (a.Delay >= ? or a.Hits >= ?)', 
            array($this->datetime, $this->date, $this->conf->param->write_offs->delay,
                $this->conf->param->write_offs->hits));
    }
        
    private function _insertStatusChanges()
    {
        $this->db->query('
            insert
                loan_status_changes (LoanId, PreviousStatus, Status, Date, Datetime, UserId, Comments)
            select 
                a.LoanId,
                ? PreviousStatus,
                ? Status,
                a.Date,
                ? Datetime,
                1 UserId,
                "" Comments
            from 
                arrears a 
            where 
                a.Date = ? and (a.Delay >= ? or a.Hits >= ?)', 
            array(self::STATUS_DISBURSED,self::STATUS_WRITTEN_OFF,
                $this->datetime,$this->date,$this->conf->param->write_offs->delay,
                $this->conf->param->write_offs->hits));
    }
        
    private function _updateLoans()
    {
        $this->db->query('
            update 
                loans 
            inner join 
                arrears on loans.LoanId = arrears.LoanId
            set 
                loans.Status = ?,
                loans.LastEditDate = ?,
                loans.LastEditUserId = 1
            where 
                arrears.Date = ? and (arrears.Delay >= ? or arrears.Hits >= ?)', 
            array(self::STATUS_WRITTEN_OFF,$this->date,$this->date,
                $this->conf->param->write_offs->delay,$this->conf->param->write_offs->hits));
    }

    private function _updateActiveLoans()
    {
        $this->db->query('
            delete from active_loans where LoanId in 
                (select LoanId from arrears where Date = ? and (Delay >= ? or Hits >= ?))', 
        array($this->date,$this->conf->param->write_offs->delay,$this->conf->param->write_offs->hits));
    }

    private function _updateArrears()
    {
        $this->db->query('
            delete from arrears where Date = ? and (Delay >= ? or Hits >= ?)', 
        array($this->date,$this->conf->param->write_offs->delay,$this->conf->param->write_offs->hits));
    }
    
}


